#    Copyright 2011 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.

#************************BEGIN Add Include Files to Path************************
import sys
IncludePath="/opt/coopip/djangoproject/include"
if(not IncludePath in sys.path):
    sys.path.append(IncludePath)


    
#************************END   Add Include Files to Path************************
from django.contrib.gis.geos import *
import os, time, psycopg2, datetime

#********************Setup psycopg2 and django section********************
PathDjangoRoot="/opt/coopip/"
if(not PathDjangoRoot in sys.path):
    sys.path.append(PathDjangoRoot)
from django.core.management import setup_environ
import djangoproject.settings
setup_environ(djangoproject.settings)
from djangoproject.models import *
from django.contrib.gis.geos import GEOSGeometry
import CIS, AMR, OMS, GIS
from django.contrib.gis.db import models
#********************Setup psycopg2 and django section********************
from django.db import transaction

import urllib
import httplib
import datetime

#a = COOPTalk.objects.filter(InResponseReadingChangedNotificationIndicator=True).exclude(LocalProcessingCompleteIndicator=True)
#print a[0].GetMeterReadFromInResponseReadingChangedNotification()

ExampleInResponseReadingChangedNotificationBody=u"""<QueryDict: {u\'<?xml version\':
[u\'"1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Header><MultiSpeakMsgHeader
UserID="DCSI_return" Pwd="DCSI_return" AppName="Optimum" AppVersion="0.1" Company="DCSI_return" xmlns="http://www.multispeak.org/Version_3.0" />
</soap:Header><soap:Body><ReadingChangedNotification xmlns="http://www.multispeak.org/Version_3.0"><changedMeterReads><meterRead objectID="35460">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35461">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35462">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35466">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35485">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35486">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35487">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID><readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead><meterRead objectID="35490">
<meterNo>16082536</meterNo><deviceID>16082536</deviceID>
<readingDate>2013-04-29T20:13:12-04:00</readingDate><posKWh>32863</posKWh></meterRead></changedMeterReads><transactionID /></ReadingChangedNotification></soap:Body></soap:Envelope>\\r\\n\']}>"""


def testmetercircuit():
    a = AMR.Serialnumber.objects.filter(serialnumber__in=['17412019', '17519361', '17814700', '17822119', '3345413', '3346365', '3346366' ,'3346367', '3346368', '3346371'])
    return a

def rotation():
    #Print Rotation Data for Meters [possibly reimport from AMR]
    SerialNumbers=['7697789', '17412009', '82500174'] #AL0.6, UMT_2.10, UMT_4.10  #, '82500245', '82500373', '82500269']
    for serialNumber in SerialNumbers:
        meterRead = MeterRead.objects.filter(Meter__SerialNumber=serialNumber)[0]
        KWHUsageDict=meterRead.GetKWHUsageDict()
        HourlyReadingNetDict=meterRead.GetHourlyReadingNetDict()
        HourlyKWHNetDict = meterRead.GetHourlyKWHNetDict()
        key = '2013-03-01'
        print str(serialNumber) 
        
        print "HourlyReadingNetDict:  " + str(HourlyReadingNetDict[key])
        print "HourlyKWHNetDict:  " + str(HourlyKWHNetDict[key])
        print "KWHUsageDict:  " + str(KWHUsageDict[key])
    return meterRead
        
#b = a()
#b.UpdateHourlyPulseToKWHConversionFactor()

def TestAdj():
    a = list(CIS.Adjdetl.objects.all().order_by('mbrno', 'billmoyr').exclude(mbrno='00000000'))
    OutFileName = '/home/technology/Desktop/out.txt'
    o = open(OutFileName, 'w')
    o.write('mbrno'.ljust(15) + 'billmoyr'.ljust(10) + 'meterread'.ljust(15) + 'energy'.ljust(10) + 'otherchg'.ljust(10) + 'voucher'.ljust(10) +
            'meterread'.ljust(10) + 'kwh'.ljust(10) +'\n')
    
    for i in a:
        o.write(str(i.mbrno).ljust(15) + str(i.billmoyr).ljust(10) + str(i.meterread).ljust(15) +
                str(i.energy).ljust(10) + str(i.otherchg).ljust(10) +
                str(i.voucher).ljust(10) + str(i.meterread).ljust(10) +
                str(i.kwh).ljust(10) + '\n')

def CreateMissingGISOutdoorLightConditions():
    CISConditions=OutdoorLightingConversion.objects.all()
    ObjectsToCreateInGIS = []
    for i in CISConditions:
        if(not GIS.Coopgiscondition.objects.filter(id=i.gisid)):
            ObjectsToCreateInGIS.append(i)
    #ReImport on each reload for testing:
    #ResetForTesting()
    gisDepartment=GIS.Coopgisconditiondepartment.objects.filter(id=2)[0]
    gisStatus=GIS.Coopgisconditionstatus.objects.filter(id=1)[0]
    for i in ObjectsToCreateInGIS:
        New=GIS.Coopgiscondition()
        New.id=i.gisid
        New.conditiondepartment=gisDepartment
        New.point= i.gispoint
        New.stationidentifier = i.gisstationidentifier
        New.tagnumber = i.gistagnumber
        New.outdoorlightmapnumber = i.gisoutdoorlightmapnumber
        New.outdoorlightmeternumber = i.gisoutdoorlightmeternumber
        New.outdoorlightmetermapnumber = i.gisoutdoorlightmetermapnumber
        New.outdoorlightidleindicator = i.gisoutdoorlightidleindicator
        New.needsretirementindicator = i.gisneedsretirementindicator
        New.outdoorlightadditionalbillingpolecount = i.gisoutdoorlightadditionalbillingpolecount
        New.outdoorlightcustomerownspoleindicator = i.gisoutdoorlightcustomerownspoleindicator
        New.outdoorlightcustomerownslightindicator = i.gisoutdoorlightcustomerownslightindicator
        New.createdatetime = i.giscreatedatetime
        New.createusername = i.giscreateusername
        New.circuitnumbers = i.giscircuitnumbers
        New.poleconditionstatus=1
        New.guyanchorconditionstatus = gisStatus
        New.insulatorconditionstatus = gisStatus
        New.conductorconditionstatus = gisStatus
        New.vaultconditionstatus = gisStatus
        New.rightofwayconditionstatus = gisStatus
        New.padmountconditionstatus = gisStatus
        New.risercableconditionstatus = gisStatus
        New.save()
        print "Jiust Saved:  " + str(New.id)



def testNetworkDevices():
    a = NetworkDevice.objects.filter(NetworkDeviceModel__Name__icontains='radio').filter(ActiveIndicator=True)
    for i in a:
        print str(i.id).ljust(10) + str(i.NetworkDeviceModel.Name).ljust(40) + str(i.AMRSubstationName)
        i.Test()
        c = NetworkDeviceTest.objects.filter(NetworkDevice=i).order_by('-DateTime')[:10]
        print str(c[0].DateTime).ljust(40) + str(c[0].ResponseIndicator)







def TruckHistorySpecialProject():
    """This report is the history for a specific truck when it was near a specific location and for how long"""
    Temp=[]
    import datetime
    TruckLogs=DV_VehicleHistory2012.objects.filter(custvehicleid='164').order_by('DateTime')
    ResultLogs=[]
    FirstLoop=True
    PointOfInterest=Point(x=-80.89127, y=33.69851, srid=4326)
    PointOfInterestFeet=PointOfInterest.transform(2273, clone=True)
    CurrentlyClose=False
    Distance=1000
    
    for i in TruckLogs:
        CurrentPoint=i.Point.transform(2273, clone=True)
        CurrentDistance = PointOfInterestFeet.distance(CurrentPoint)
        if(CurrentDistance < Distance):
            if(CurrentlyClose == False):
                CurrentlyClose = True
                print "Truck " + str(i.custvehicleid) + " Approaching:  ".ljust(18) + str(int(CurrentDistance)) + " ft Starting At:  " + str(i.DateTime)[:19]
                ApproachDateTime=i.DateTime
        elif(CurrentlyClose == True):
                print "Truck " + str(i.custvehicleid) + " Leaving:  ".ljust(18) + str(int(CurrentDistance)) + " ft Starting At:  " + str(i.DateTime)[:19].ljust(22) + str((i.DateTime - ApproachDateTime).seconds / 60) + " Minutes"
                CurrentlyClose = False
                

@transaction.commit_on_success
def CopyTable():
    Count = 0
    
    OldList=VehicleHistory201209.objects.filter(custvehicleid='164')
    Total = OldList.count()
    for Old in OldList:
        Count = Count + 1
        if(Count % 1000) == 0:
            print "On:  " + str(Count).ljust(20) + 'of    ' + str(Total)
        New=DV_VehicleHistory2012()
        New.utctime=Old.utctime
        New.longitude_x=Old.longitude_x
        New.latitude_y=Old.latitude_y
        New.quality=Old.quality
        New.satellites=Old.satellites
        New.altitude=Old.altitude
        New.truecourse=Old.truecourse
        New.magneticcourse=Old.magneticcourse
        New.speed=Old.speed
        New.avlvehicleid=Old.avlvehicleid
        New.status=Old.status
        New.gos_id=Old.gos_id
        New.recordid=Old.recordid
        New.vehicleid=Old.vehicleid
        New.custvehicleid=Old.custvehicleid
        New.serviceindex=Old.serviceindex
        New.vehicledescription=Old.vehicledescription
        New.vehicleequipment=Old.vehicleequipment
        New.vehicleradio=Old.vehicleradio
        New.crewid=Old.crewid
        New.gpstimestamp=Old.gpstimestamp
        New.clienttimestamp=Old.clienttimestamp
        New.direction=Old.direction
        New.neareststreet=Old.neareststreet
        New.odometer=Old.odometer
        New.make=Old.make
        New.model=Old.model
        New.radio=Old.radio
        New.DateTime=Old.DateTime
        New.Point=Old.Point
        New.save()
        



    




def SearchInMeter(SerialNumber, PrioritySubstationName=None):
    newmeter=djangoproject.models.ipbase.MeterSearch()
    newmeter.SerialNumber=SerialNumber
    if(PrioritySubstationName):
        newmeter.PrioritySubstation=djangoproject.models.amrcache.Substation.objects.filter(Name__iexact=PrioritySubstationName)[0]
        newmeter.PrioritySubstationIndicator=True
    else:
        newmeter.PrioritySubstationIndicator=False
    newmeter.StartDateTime=datetime.datetime.now()
    newmeter.OutToAMRCount=0
    newmeter.PriorityLevel=1
    newmeter.save()
    
def ImportMetersFromAMR():
    meterModel=MeterModel()
    meterModel.GetMeterModelsFromAMR()
    SerialNumbers=AMR.SerialNumber.objects.all().filter(devicetype=0)
    for serialNumber in SerialNumbers:
        New=Meter()
        New=New.AddUpdateMeterFromAMR(serialNumber.serialnumber)


        
        
def AssignMetersToGroups():
    Substations=Substation.objects.all()
    CurrentGroup = 1
    MaxGroup = 100
    for i in range(CurrentGroup, MaxGroup + 1):
        if(not PingGroup.objects.filter(PingGroupInteger = i)):
            New=PingGroup()
            New.PingGroupInteger=i
            New.save()
    
    for substation in Substations:  #cannot ping a meter if it does not have a substation
        Meters=Meter.objects.filter(Substation=substation)
        for meter in Meters:
            meter.PingGroup = CurrentGroup
            meter.save()
            CurrentGroup = CurrentGroup + 1
            if(CurrentGroup > MaxGroup):
                CurrentGroup = 1
       
@transaction.commit_on_success
def ImportMeterBlinks():
    import GOS
    OldestDateTime=datetime.datetime(2001, 1, 1)
    OldestBlinks=MeterBlink.objects.all().order_by('-DateTime')
    if(OldestBlinks):
        OldestDateTime=OldestBlinks[0].DateTime
    Blinks=GOS.Blink.objects.all().filter(read_time__gt=OldestDateTime)
    for i in Blinks:
        New=MeterBlink()
        New.SerialNumber = i.serialnumber
        New.Count = i.blink_count
        New.DateTime = i.read_time
        New.save()


        

def UpdateMeterReadings():
    Cache=AMRDBCache()
    #all.delete()?  Want historical data, doubtful
    startdate=datetime.datetime.now() - datetime.timedelta(days=1)
    Meters=Meter.objects.all()#already updated from their own cron
    for meter in Meters:
        MeterReading().Update(meter, startdate, Cache)


def GetARPCacheList(Destination, UserName, Password):
    import telnetlib, time, re
    tn=telnetlib.Telnet(Destination)
    tn.read_very_eager()
    time.sleep(2)
    tn.write(UserName + '\r')
    tn.read_until('word:')
    tn.write(Password + '\r')
    tn.read_very_eager()
    tn.write('terminal length 0\r')
    tn.write('clear arp-cache\r')
    #TODO ping subnet to populate arp-cache
    tn.write('show arp\r')
    Output=''
    Tries=0
    while len(Output) < 1000 and Tries < 10:
        print Output
        Output=tn.read_very_eager()
        time.sleep(2)
        Tries+=1
        print Tries
    Output=Output.split('\r\n')
    ReturnList=[]
    for i in Output[6:]:
        try:
            clearentry=re.sub(r'\s+', ' ', i)
            clearentry=clearentry.split(' ')
            ReturnList.append((clearentry[1], clearentry[3]))
        except:
            continue
    return ReturnList

def PingOneHour(IPAddress=None):
    import os
    os.chdir('/scripts')
    LogFile=open('PingLog', w)
    import subprocess, shlex, datetime, time
    if(not IPAddress):
        IPAddress=str(self.IPAddress)
    Failures=[]
    Hence=datetime.datetime.now()+datetime.timedelta(hours=1)
    while datetime.datetime.now()<Hence:
        time.sleep(1)
        try:
            rawCommand=r'ping -c 1 ' + IPAddress
            shellCommand=shlex.split(rawCommand)
            commandOutput=subprocess.check_output(shellCommand)
            start=commandOutput.index('time=') + 5
            end=commandOutput[start:].index('ms')
            pingAsFloat=float(commandOutput[start:(start+end)])
            SuccessIndicator=True
            sometime=pingAsFloat
            print sometime
        except:
            print commandOutput
            Failures.append(commandOutput + '\n')
    LogFile.writelines(Failures)
    
    


def TalkStressTest(ID, MeterNumber):
    IPAddress= '192.168.100.98:80'
    Header = {"Content-type":"application/soap+xml", "charset":"utf-8"}
    URL='/Optimum_Web_Service/MultiSpeak/30/MR_Server.asmx'
    Body="""<?xml version="1.0" encoding="utf-8"?>
                    <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
                    <soap12:Header>
                    <MultiSpeakMsgHeader Version="3.0" UserID="DCSI" Pwd="DCSI"
                    AppName="COOPIP"
                    AppVersion="1" Company="TCEC" CSUnits="feet"
                    CoordinateSystem=""
                    Datum=""
                    xmlns="http://www.multispeak.org/Version_3.0" />
                    </soap12:Header>
                    <soap12:Body>
                    <GetLatestReadingByMeterNo xmlns="http://www.multispeak.org/Version_3.0">
                    <meterNo>{{MeterNumber}}</meterNo>
                    </GetLatestReadingByMeterNo>
                    </soap12:Body>
                    </soap12:Envelope>""".replace('{{MeterNumber}}', MeterNumber)

    Connection=httplib.HTTPConnection(IPAddress)
    Start = datetime.datetime.now()
    Connection.request("POST", URL, Body, Header)
    Response = Connection.getresponse()
    Result = Response.read()
    End = datetime.datetime.now()
    Duration = (End-Start).total_seconds()
    if 'error' in Result:
        WriteToLog(ID, MeterNumber = MeterNumber)
    else:
        WriteToLog(ID, Duration = Duration)

def WriteToLog(ID, Duration=0, MeterNumber=''):
    Log = open('/home/technology/Desktop/StressLog', 'a')
    if MeterNumber:
        Log.write('ERROR ' + ID + ' ' + MeterNumber + '\n')
    else:
        Log.write('Success ' + ID + ' ' + str(Duration) + '\n')
    Log.close()

def DeviceOutageTest():
    DeviceIncidents=list(OMS.DVIncident.objects.filter(deviceflag=1))
    OpenIncidents=list(OMS.DVIncident.objects.filter(deviceflag=0).order_by('substationnumber', 'feedernumber'))
    DeviceDataTuples=[]
    ChildPool=[]
    for device in DeviceIncidents:
        SubordinateIncident=None
        #find subordinate incidents by linesection, add to list to handle differently in SummaryReport
        ParentPool = [device.linesection]
        DeviceChildPool=[]
        while ParentPool:
            print str(len(ParentPool))
            Children=OMS.Lineconnectivity.objects.filter(parentlinesection__in=ParentPool)
            ParentPool=[]
            if Children.count():
                ParentPool = list(Children.values_list('linesection', flat=True))
                continue
            DeviceChildPool += ParentPool
        for i in DeviceChildPool:
            if SubordinateIncident:
                break
            OpenIncidentsAtSection=OMS.DVIncident.objects.filter(deviceflag=0).filter(linesection=i)
            if OpenIncidentsAtSection.count():
                SubordinateIncident=OpenIncidentsAtSection[0]
        DeviceDataTuples.append((device.customersaffected, SubordinateIncident))
        ChildPool += DeviceChildPool


def TestOldMap():
    Temp = []
    Start = datetime.datetime.now()
    print Start
    Locations = list(CIS.Locinfodetl.objects.all())
    LocationList = []
    for i in Locations:
        LocationList.append((i.cycle, i.distcode, i.location, i))
    LocationList.sort()
    Step1 = datetime.datetime.now()
    print Step1
    Temp.append('Cycle'.rjust(10) + 'District'.rjust(10) + 'Location'.rjust(20) + 'Station'.rjust(15) +
                'Board District'.rjust(15) + 'Substation'.rjust(10) + 'CIS County Code'.rjust(15) + 'GIS County Name'.rjust(15))
    for sublist in LocationList[:1500]:
        i = sublist[3]
        Cycle = str(i.cycle)
        District = str(i.distcode)
        Location = str(i.location).lstrip('0')
        try:
            Station = str(GIS.Coopgisassembly.objects.get(meterlocationnumberfromcis = Location).id)
        except:
            Station = ''
        try:
            BoardDistrict = str(GIS.Coopgisboarddistrict.objects.get(cisidentifier=District).name)
        except:
            BoardDistrict = ''
        Substation = str(i.substation)
        County = str(i.countycode)
        try:
            GISCounty = str(GIS.Coopgiscounty.objects.get(cisidentifier=County).name)
        except:
            GISCounty = ''
        Temp.append(Cycle.rjust(10) + District.rjust(10) + Location.rjust(20) + Station.rjust(15) + BoardDistrict.rjust(15) + Substation.rjust(10) + County.rjust(15) + GISCounty.rjust(15))
    End = datetime.datetime.now()
    Temp.append('Start: ' + str(Start))
    Temp.append('List: ' + str(Step1))
    Temp.append('End: ' + str(End))
    return Temp

def ImportNewAVL():
    Input = open('/opt/coopip/scripts/BackupMarch.backup', 'r')
    RawInput = Input.readlines()
    Input.close()
    return RawInput[:20]

def ParseAVL(Input):
    In1 = Input.split('\t')
    new = VehicleHistory201303()
    new.utctime = In1[23]
    new.longitude_x=In1[25]
    new.latitude_y=In1[26]
    new.quality = ''
    new.direction = In1[32]
    new.speed=float(In1[31])
    new.save()

def SetCircuitLabelPoints():
    import GIS
    from django.contrib.gis.geos import GEOSGeometry
    for i in DispatchMapLabel.objects.all():
        Symbologies = GIS.Coopgissymbologyprimaryline.objects.filter(circuitnumber=i.Name)
        if not Symbologies.count():
            continue
        Bounds = Symbologies.extent()
        Minx, Miny, Maxx, Maxy = Bounds
        XDiff = (Maxx - Minx) / 2.0
        YDiff = (Maxy - Miny) / 2.0
        Newx = Maxx - XDiff
        Newy = Maxy - YDiff
        BuildPoint = GEOSGeometry('POINT(' + str(Newx) + ' ' + str(Newy) + ')')
        BuildPoint.srid=2273
        i.Point = BuildPoint
        i.save()
    

        
